{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Guest House - Medium"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "········\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@192.168.31.31:15432/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "booking = pd.read_sql_table('booking', engine)\n",
    "guest = pd.read_sql_table('guest', engine)\n",
    "room = pd.read_sql_table('room', engine)\n",
    "rate = pd.read_sql_table('rate', engine)\n",
    "extra = pd.read_sql_table('extra', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6.\n",
    "Ruth Cadbury. Show the total amount payable by guest Ruth Cadbury for her room bookings. You should JOIN to the rate table using room_type_requested and occupants.\n",
    "\n",
    "```\n",
    "+--------------------+\n",
    "| SUM(nights*amount) |\n",
    "+--------------------+\n",
    "|             552.00 |\n",
    "+--------------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(nightsa*amount)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>552.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(nightsa*amount)\n",
       "0                552.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (guest.loc[(guest['first_name']=='Ruth') & \n",
    "           (guest['last_name']=='Cadbury'), :]\n",
    " .merge(booking, left_on='id', right_on='guest_id')\n",
    " .merge(rate, left_on=['room_type_requested', 'occupants'], \n",
    "        right_on=['room_type', 'occupancy']))\n",
    "\n",
    "pd.DataFrame({'SUM(nightsa*amount)': [sum(a['nights']*a['amount'])]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7.\n",
    "Including Extras. Calculate the total bill for booking 5346 including extras.\n",
    "\n",
    "```\n",
    "+-------------+\n",
    "| SUM(amount) |\n",
    "+-------------+\n",
    "|      118.56 |\n",
    "+-------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(amount)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>118.56</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(amount)\n",
       "0       118.56"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (booking.loc[booking['booking_id']==5346, :]\n",
    "     .merge(rate, left_on=['occupants', 'room_type_requested'],\n",
    "            right_on=['occupancy', 'room_type'])\n",
    "    [['booking_id', 'amount']])\n",
    "b = (booking.loc[booking['booking_id']==5346, :]\n",
    "     .merge(extra, on='booking_id')\n",
    "    [['booking_id', 'amount']])\n",
    "c = pd.concat([a, b])\n",
    "\n",
    "pd.DataFrame({'SUM(amount)': [sum(c['amount'])]})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8.\n",
    "Edinburgh Residents. For every guest who has the word “Edinburgh” in their address show the total number of nights booked. Be sure to include 0 for those guests who have never had a booking. Show last name, first name, address and number of nights. Order by last name then first name.\n",
    "\n",
    "```\n",
    "+-----------+------------+---------------------------+--------+\n",
    "| last_name | first_name | address                   | nights |\n",
    "+-----------+------------+---------------------------+--------+\n",
    "| Brock     | Deidre     | Edinburgh North and Leith |      0 |\n",
    "| Cherry    | Joanna     | Edinburgh South West      |      0 |\n",
    "| Murray    | Ian        | Edinburgh South           |     13 |\n",
    "| Sheppard  | Tommy      | Edinburgh East            |      0 |\n",
    "| Thomson   | Michelle   | Edinburgh West            |      3 |\n",
    "+-----------+------------+---------------------------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>last_name</th>\n",
       "      <th>first_name</th>\n",
       "      <th>address</th>\n",
       "      <th>nights</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Brock</td>\n",
       "      <td>Deidre</td>\n",
       "      <td>Edinburgh North and Leith</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cherry</td>\n",
       "      <td>Joanna</td>\n",
       "      <td>Edinburgh South West</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Murray</td>\n",
       "      <td>Ian</td>\n",
       "      <td>Edinburgh South</td>\n",
       "      <td>13.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Sheppard</td>\n",
       "      <td>Tommy</td>\n",
       "      <td>Edinburgh East</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Thomson</td>\n",
       "      <td>Michelle</td>\n",
       "      <td>Edinburgh West</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  last_name first_name                    address  nights\n",
       "2     Brock     Deidre  Edinburgh North and Leith     0.0\n",
       "3    Cherry     Joanna       Edinburgh South West     0.0\n",
       "0    Murray        Ian            Edinburgh South    13.0\n",
       "4  Sheppard      Tommy             Edinburgh East     0.0\n",
       "1   Thomson   Michelle             Edinburgh West     3.0"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(booking.merge(guest.loc[guest['address'].str.contains('Edinburgh'), :],\n",
    "               left_on='guest_id', right_on='id', how='right')\n",
    " .groupby(['id', 'last_name', 'first_name', 'address'])\n",
    " .agg({'nights': 'sum'})\n",
    " .reset_index()\n",
    " [['last_name', 'first_name', 'address', 'nights']]\n",
    " .sort_values(['last_name', 'first_name']))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9.\n",
    "How busy are we? For each day of the week beginning 2016-11-25 show the number of bookings starting that day. Be sure to show all the days of the week in the correct order.\n",
    "\n",
    "```\n",
    "+------------+----------+\n",
    "| i          | arrivals |\n",
    "+------------+----------+\n",
    "| 2016-11-25 |        7 |\n",
    "| 2016-11-26 |        8 |\n",
    "| 2016-11-27 |       12 |\n",
    "| 2016-11-28 |        7 |\n",
    "| 2016-11-29 |       13 |\n",
    "| 2016-11-30 |        6 |\n",
    "| 2016-12-01 |        7 |\n",
    "+------------+----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>booking_date</th>\n",
       "      <th>arrivals</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2016-11-25</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-11-26</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-11-27</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-11-28</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-11-29</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-11-30</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-12-01</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  booking_date  arrivals\n",
       "0   2016-11-25         7\n",
       "1   2016-11-26         8\n",
       "2   2016-11-27        12\n",
       "3   2016-11-28         7\n",
       "4   2016-11-29        13\n",
       "5   2016-11-30         6\n",
       "6   2016-12-01         7"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(booking.loc[(booking['booking_date'] >= '2016-11-25') &\n",
    "             (booking['booking_date'] <= pd.Timestamp('2016-11-25') + pd.Timedelta(days=6)), :]\n",
    " .groupby('booking_date')\n",
    " .agg(arrivals=('booking_id', 'count'))\n",
    " .reset_index())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10.\n",
    "How many guests? Show the number of guests in the hotel on the night of 2016-11-21. Include all occupants who checked in that day but not those who checked out.\n",
    "\n",
    "```\n",
    "+----------------+\n",
    "| SUM(occupants) |\n",
    "+----------------+\n",
    "|             39 |\n",
    "+----------------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SUM(occupants)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>39</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   SUM(occupants)\n",
       "0              39"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "a = (booking.loc[(booking['booking_date'] <= '2016-11-21') &\n",
    "             (booking['booking_date'] + \n",
    "              pd.to_timedelta(booking['nights'], unit='D') > \n",
    "              pd.Timestamp('2016-11-21')), :])\n",
    "pd.DataFrame({'SUM(occupants)': [sum(a['occupants'])]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
